﻿DECLARE @Var_Flag INT
DECLARE @Var_RervationDate DATE
DECLARE @Var_SecondDeptCode VARCHAR(100)

SET @Var_Flag=(1)
SET @Var_RervationDate = @ReservationDate_In 
SET @Var_SecondDeptCode = @SecondDeptCode_In

SELECT RTRIM(Code) AS KSCode
      ,CASE Name WHEN '内一科' THEN '神经内科、（囊虫病科）' 
                   WHEN '内二科' THEN '呼吸、消化内科'
                   WHEN '内三科' THEN '心血管、内分泌内科'
                   WHEN '内四科' THEN '感染性疾病科'
                   WHEN '内五科' THEN '针灸、推拿、睡眠医学科'
                   WHEN '精神科' THEN '精神康复科'
                   WHEN '外一科' THEN '普外、肿瘤科'
                   WHEN '外二科' THEN '泌尿外科'
                   WHEN '外三科' THEN '骨科'
                   WHEN '眼科' THEN '眼耳鼻喉科'
                   WHEN '中痔科' THEN '肛肠科'
                   WHEN '急诊' THEN '急诊科；重症医学科'
        ELSE Name              
        END AS KSName
      ,RTRIM(LBName) AS Description
      ,RTRIM(KSAddress) AS Address
  FROM KSCODE (NOLOCK)
 WHERE KSATTRIB IN(1,2,4) AND
       STOPDATE IS NULL AND
       CODE LIKE '%' + @SecondDeptCode_In AND
       CODE IN (SELECT KSCODE
                  FROM MZSCHEDULE (NOLOCK)
                 WHERE YSCODE<>'*' + @Var_SecondDeptCode AND 
                      (@Var_Flag=0 OR (@Var_Flag=1 AND WEEKDAY=DATEPART(WEEKDAY,@Var_RervationDate))) AND
                       (STATUS IS NULL OR STATUS = 2)
               )